1
00:00:01,420 --> 00:00:03,580
In this lesson, we're
going to look at data

2
00:00:03,580 --> 00:00:06,280
migration using SQL Loader.

3
00:00:06,280 --> 00:00:08,800
So data migration is
the act of moving data

4
00:00:08,800 --> 00:00:13,450
from one place to another and
often from an external source

5
00:00:13,450 --> 00:00:15,580
into an Oracle database.

6
00:00:15,580 --> 00:00:19,250
So there are numerous ways
to load data into a database.

7
00:00:19,250 --> 00:00:21,740
So we're going to take
a look at SQL Loader.

8
00:00:21,740 --> 00:00:25,330
So SQL Loader is somewhat
of an older tool,

9
00:00:25,330 --> 00:00:27,550
but it's still very
commonly used today

10
00:00:27,550 --> 00:00:31,480
because it's really, really
good at taking file data, just

11
00:00:31,480 --> 00:00:35,470
straight delimited data,
let's say, or even raw data,

12
00:00:35,470 --> 00:00:37,630
and loading it into tables.

13
00:00:37,630 --> 00:00:39,730
There's quite a bit of
work in setting it up.

14
00:00:39,730 --> 00:00:41,980
But once you do that
work, it's something

15
00:00:41,980 --> 00:00:44,920
that doesn't have to be repeated
unless your table changes,

16
00:00:44,920 --> 00:00:46,970
and it can be very, very fast.

17
00:00:46,970 --> 00:00:52,070
So SQL Loader is going to read
data from a delimited file

18
00:00:52,070 --> 00:00:54,290
and load it into a table.

19
00:00:54,290 --> 00:00:57,700
So a delimited file would be
just a file where the data is

20
00:00:57,700 --> 00:01:02,050
delimited by some delimiter,
like a comma-delimited file,

21
00:01:02,050 --> 00:01:05,200
or pipe delimited, or
any number of characters,

22
00:01:05,200 --> 00:01:08,380
and you just need to specify
what the delimiter is.

23
00:01:08,380 --> 00:01:11,560
It can also read
data from flat files

24
00:01:11,560 --> 00:01:15,620
and load it into tables
based on character positions.

25
00:01:15,620 --> 00:01:19,150
So it can say, take the first
five characters of the file

26
00:01:19,150 --> 00:01:21,730
and load that into the first
column, and then the next 10,

27
00:01:21,730 --> 00:01:25,570
and load that into the second
column, so on and so forth.

28
00:01:25,570 --> 00:01:28,270
And often this is used
with mainframe data,

29
00:01:28,270 --> 00:01:32,080
in conjunction with mainframe
data, where the Oracle

30
00:01:32,080 --> 00:01:35,680
database is in the mid tier,
kind of, of the enterprise

31
00:01:35,680 --> 00:01:40,680
and receives data that's
stored in a mainframe system.

32
00:01:40,680 --> 00:01:44,390
So SQL Loader can run using
two different methods.

33
00:01:44,390 --> 00:01:46,480
The first is the
conventional path,

34
00:01:46,480 --> 00:01:48,430
and all that the
conventional path does

35
00:01:48,430 --> 00:01:51,640
is simply construct
INSERT statements using

36
00:01:51,640 --> 00:01:54,880
the delimited or raw
data and then just run

37
00:01:54,880 --> 00:01:56,530
those INSERT statements.

38
00:01:56,530 --> 00:01:59,050
So conventional
path is the safest,

39
00:01:59,050 --> 00:02:01,840
if you would, but
only in terms of being

40
00:02:01,840 --> 00:02:05,050
able to use all kinds
of different data types.

41
00:02:05,050 --> 00:02:08,860
There's nothing inherently
unsafe about either way,

42
00:02:08,860 --> 00:02:13,060
or either method, except that
the second method, direct path,

43
00:02:13,060 --> 00:02:16,990
can't necessarily use some of
the more esoteric data types

44
00:02:16,990 --> 00:02:19,900
that people sometimes
have in a database.

45
00:02:19,900 --> 00:02:23,170
Direct path, however,
constructs blocks

46
00:02:23,170 --> 00:02:26,920
from that data that are written
directly into the database.

47
00:02:26,920 --> 00:02:30,790
And it actually bypasses
the database buffer cache

48
00:02:30,790 --> 00:02:32,860
and the caching
that needs to occur.

49
00:02:32,860 --> 00:02:37,370
And for that reason,
direct path is much faster.

50
00:02:37,370 --> 00:02:40,750
So it doesn't have to construct
and run INSERT statements.

51
00:02:40,750 --> 00:02:43,630
It just constructs the blocks
that are directly written.

52
00:02:43,630 --> 00:02:45,820
And so whenever
possible, we definitely

53
00:02:45,820 --> 00:02:47,350
want to use the direct path.

54
00:02:47,350 --> 00:02:49,730
And we should be able
to in most cases,

55
00:02:49,730 --> 00:02:52,750
unless there's something
sort of unusual

56
00:02:52,750 --> 00:02:54,910
about the table that
we're loading into

57
00:02:54,910 --> 00:02:57,730
or the data that we have.

58
00:02:57,730 --> 00:03:01,020
So there are a number of files
that are used in a SQL Loader

59
00:03:01,020 --> 00:03:01,770
operation.

60
00:03:01,770 --> 00:03:05,670
So all these have to be
prepared before we run our SQL

61
00:03:05,670 --> 00:03:08,190
Loader, which the statement
itself is quite simple,

62
00:03:08,190 --> 00:03:10,660
but we have to compose
all of these files.

63
00:03:10,660 --> 00:03:13,300
So the first one is the
input data file itself.

64
00:03:13,300 --> 00:03:16,260
So that's going to be the
delimited data or raw data

65
00:03:16,260 --> 00:03:19,230
that we're trying to
load into a table.

66
00:03:19,230 --> 00:03:21,390
The second is the
parameter file,

67
00:03:21,390 --> 00:03:24,780
and that's a file that
just has a location for all

68
00:03:24,780 --> 00:03:28,230
the other files, so the input
data file, the output files,

69
00:03:28,230 --> 00:03:32,580
like the log, and those kind
of things, the control file.

70
00:03:32,580 --> 00:03:36,850
The parameter file is what knows
all about those other files.

71
00:03:36,850 --> 00:03:38,910
And it's actually
the parameter file

72
00:03:38,910 --> 00:03:42,510
that we're going to directly
call from the SQL Loader

73
00:03:42,510 --> 00:03:46,470
application, and then it's
going to find everything else.

74
00:03:46,470 --> 00:03:48,100
And the third is
the control file.

75
00:03:48,100 --> 00:03:49,860
So that's actually
a specification

76
00:03:49,860 --> 00:03:52,990
on how the data is to be loaded.

77
00:03:52,990 --> 00:03:54,120
So let's take a look here.

78
00:03:54,120 --> 00:03:55,890
I've got a little
bit of setup here

79
00:03:55,890 --> 00:03:58,560
already in the
Oracle-based directory.

80
00:03:58,560 --> 00:04:01,350
I've created a
directory called loader.

81
00:04:01,350 --> 00:04:02,370
All right.

82
00:04:02,370 --> 00:04:04,580
Click that.

83
00:04:04,580 --> 00:04:08,750
And then I have a
dept load.dat file.

84
00:04:08,750 --> 00:04:11,730
If we take a look at
that, that's simply

85
00:04:11,730 --> 00:04:13,530
data from the dept table.

86
00:04:13,530 --> 00:04:16,920
So we just poured that
out to a DAT file.

87
00:04:16,920 --> 00:04:20,910
And then, so it's comma
delimited-- three columns,

88
00:04:20,910 --> 00:04:21,990
comma-delimited file.

89
00:04:21,990 --> 00:04:25,110
So that's what we're going to
use for our loader, our load

90
00:04:25,110 --> 00:04:27,140
data.

91
00:04:27,140 --> 00:04:31,490
Let's go into Scott and create
a table to receive this data.

92
00:04:37,460 --> 00:04:40,570
So we'll create a table that's
just like the dept table.

93
00:04:54,510 --> 00:05:00,120
So if we select from
the dept_loader table,